package com.qiyang.test;

import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import com.qiyang.mapper.UserMapper;
import com.qiyang.pojo.User;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.RowBounds;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Before;
import org.junit.Test;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

/**
 * Created on 2020/12/13.
 *
 * @author qiyang.
 * 描述：演示增删改查
 */
public class MybatisCRUD {

    Logger log = LoggerFactory.getLogger(MybatisTest.class);

    private SqlSessionFactory sqlSessionFactory;

    /**
     * Mybatis3之前的版本使用方式，前身ibatis
     *
     * @throws IOException
     */
    @Before
    public void before() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    }

    /**
     * 基于接口的方式
     * 插入数据
     *
     * @throws IOException 可以通过：openSession(true) 自动提交事务
     */
    @Test
    public void insert() {
        try (SqlSession session = sqlSessionFactory.openSession()) {
            UserMapper userMapper = session.getMapper(UserMapper.class);
            // 基于XML方式
            try {
                Integer row = userMapper.insert(new User(1003L, "qiyang"));
                // 手动提交事务
                session.commit();

                System.out.println("row = " + row);
            } catch (Exception ex) {
                session.rollback();
                ex.printStackTrace();
            } finally {
                session.close();
            }
        }
    }

    /**
     * 基于接口的方式
     * 插入数据
     *
     * @throws IOException 可以通过：openSession(true) 自动提交事务
     */
    @Test
    public void update() {
        try (SqlSession session = sqlSessionFactory.openSession()) {
            UserMapper userMapper = session.getMapper(UserMapper.class);
            // 基于XML方式
            try {
                Integer row = userMapper.update(new User(1003L, "qiyang-update"));
                // 手动提交事务
                session.commit();

                System.out.println("row = " + row);
            } catch (Exception ex) {
                session.rollback();
                ex.printStackTrace();
            } finally {
                session.close();
            }
        }
    }

    /**
     * 基于接口的方式
     * 插入数据
     *
     * @throws IOException 可以通过：openSession(true) 自动提交事务
     */
    @Test
    public void delete() {
        try (SqlSession session = sqlSessionFactory.openSession()) {
            UserMapper userMapper = session.getMapper(UserMapper.class);
            // 基于XML方式
            try {
                Integer row = userMapper.delete(1003L);
                // 手动提交事务
                session.commit();

                System.out.println("row = " + row);
            } catch (Exception ex) {
                session.rollback();
                ex.printStackTrace();
            } finally {
                session.close();
            }
        }
    }

    /**
     * 基于接口的方式
     * 插入数据
     *
     * @throws IOException 可以通过：openSession(true) 自动提交事务
     */
    @Test
    public void insert2() {
        try (SqlSession session = sqlSessionFactory.openSession()) {
            UserMapper userMapper = session.getMapper(UserMapper.class);
            // 基于XML方式
            try {
                User insertUser = new User("刘永康2");
                Integer row = userMapper.insertSelective(insertUser);
                log.debug("新插入的 id = {}", insertUser.getId());
                // 手动提交事务
                session.commit();

                System.out.println("row = " + row);
            } catch (Exception ex) {
                session.rollback();
                ex.printStackTrace();
            } finally {
                session.close();
            }
        }
    }

    /**
     * 缓存演示
     * 一级缓存默认开启，基于SqlSession作用域，可通过LocalCacheScope参数对其进行关闭，基本不会关闭
     * 同一个SqlSession，若查询语句不一样，缓存也不会生效
     * 缓存的key = 通过statement生成 hashcode + sqlid + sql
     * 缓存默认实现类：PerpetualCache
     * 同SqlSession，查询语句也一直，但期间数据被做了增删改，此时缓存也会失效
     */
    @Test
    public void selectCache() {
        try (SqlSession session = sqlSessionFactory.openSession()) {
            UserMapper userMapper = session.getMapper(UserMapper.class);
            // 基于XML方式
            try {
                User user = new User(1L);
                List<User> users1 = userMapper.selectCacheUser(user);
                System.out.println("完成第1次查询");
                List<User> users2 = userMapper.selectCacheUser(user);
                System.out.println("完成第2次查询，会走缓存");
                User user3 = new User("qiyang409");
                List<User> users3 = userMapper.selectCacheUser(user3);
                System.out.println("完成第3次查询，不会走缓存");

                User insertUser = new User("张小玲");
                Integer row = userMapper.insertSelective(insertUser);
                log.debug("新插入的 id = {}", insertUser.getId());
                // 手动提交事务
                session.commit();

                List<User> users4 = userMapper.selectCacheUser(user3);
                System.out.println("完成第4次查询，不会走缓存");

                // 手动清理缓存，也会导致缓存失效
                session.clearCache();

            } catch (Exception ex) {
                session.rollback();
                ex.printStackTrace();
            } finally {
                session.close();
            }
        }
    }

    /**
     * 二级缓存演示，注意有缓存命中率
     * 缓存顺序：一级缓存在select后触发，二级缓存在事务提交后再触发
     * 获取缓存顺序：先从二级缓存，再从一级缓存拿
     * 失效：同一个命名空间，增删改操作会导致二级缓存失效，或对select 标签设置 useCache=false 默认为 ture
     * 不失效：可对insert update delete 时设置属性 flushCache=false 也不让缓存失效，默认为true
     */
    @Test
    public void select2Cache() {
        try (SqlSession session = sqlSessionFactory.openSession()) {
            UserMapper userMapper = session.getMapper(UserMapper.class);
            // 基于XML方式
            try {
                User user = new User(1L);
                List<User> users1 = userMapper.selectCacheUser(user);
                System.out.println("完成第1次查询");
            } catch (Exception ex) {
                session.rollback();
                ex.printStackTrace();
            } finally {
                session.close();
            }
        }

        try (SqlSession session = sqlSessionFactory.openSession()) {
            UserMapper userMapper = session.getMapper(UserMapper.class);
            // 基于XML方式
            try {
                User user = new User(1L);
                List<User> users2 = userMapper.selectCacheUser(user);
                System.out.println("完成第2次查询");
            } catch (Exception ex) {
                session.rollback();
                ex.printStackTrace();
            } finally {
                session.close();
            }
        }
    }

    /**
     * 分页查询演示
     */
    @Test
    public void selectPage() {
        try (SqlSession session = sqlSessionFactory.openSession()) {
            UserMapper userMapper = session.getMapper(UserMapper.class);

            try {
                // 分页查询
                PageHelper.startPage(1, 10);

                // 返回的其实是Page类的实例
                List<User> users = userMapper.queryPage();
                System.out.println(users);
                // 获取更多属性，转换成PageInfo
                PageInfo pageInfo = new PageInfo(users, 5);
                pageInfo.isIsFirstPage();
                pageInfo.isIsLastPage();
                pageInfo.getPageNum();
                pageInfo.getNavigatepageNums();
                // ...前端分页组件需要的数据几乎全了

            } catch (Exception ex) {
                session.rollback();
                ex.printStackTrace();
            } finally {
                session.close();
            }
        }
    }

    /**
     * 调用自定义分页查询演示
     */
    @Test
    public void selectMyPage() {
        try (SqlSession session = sqlSessionFactory.openSession()) {
            UserMapper userMapper = session.getMapper(UserMapper.class);

            try {
                // 自定义分页查询
                List<User> users = userMapper.queryPage(new RowBounds(0, 5));
                System.out.println(users);

            } catch (Exception ex) {
                session.rollback();
                ex.printStackTrace();
            } finally {
                session.close();
            }
        }
    }
}
